# 从文件中读取所有插入语句
with open('insert_statements.txt', 'r', encoding='utf-8') as file:
    insert_statements = file.readlines()

data = []
for stmt in insert_statements:
    stmt = stmt.strip()
    if not stmt.endswith(';'):
        continue  # 跳过不完整的语句
    try:
        # 提取 VALUES 部分
        parts = stmt.split("VALUES (")[1].strip(');')
        parts = parts.split(', ')

        # 提取字段值
        id_val = parts[0]
        level_1 = parts[1].strip("'")
        level_2 = parts[2].strip("'")
        level_3 = parts[3].strip("'")
        sort_1 = int(parts[4])
        sort_2 = int(parts[5])
        sort_3 = int(parts[6])

        # 存储数据
        data.append({
            'id': id_val,
            'level_1': level_1,
            'level_2': level_2,
            'level_3': level_3,
            'sort_1': sort_1,
            'sort_2': sort_2,
            'sort_3': sort_3
        })
    except IndexError:
        print(f"Skipping invalid statement: {stmt}")

# 聚合数据
unique_level1 = set(row['level_1'] for row in data)
unique_level2 = set((row['level_1'], row['level_2']) for row in data)
unique_level3 = set((row['level_1'], row['level_2'], row['level_3']) for row in data)

# 生成插入语句
insert_kind = []
kind_id_map = {}
current_id = 1

# 第一层：level_1
for level1 in unique_level1:
    # 找到 level1 对应的最小 sort_1
    min_sort_1 = min(row['sort_1'] for row in data if row['level_1'] == level1)

    # 生成插入语句
    insert_kind.append(
        f"INSERT INTO cms_kind_1 (id, kind, parent_id, level, sort) VALUES ({current_id}, '{level1}', NULL, 1, {min_sort_1});")
    kind_id_map[(level1,)] = current_id
    current_id += 1

# 第二层：level_2
for level1, level2 in unique_level2:
    parent_id = kind_id_map.get((level1,), None)
    if parent_id is not None:
        # 找到 level2 对应的最小 sort_2
        min_sort_2 = min(row['sort_2'] for row in data if row['level_1'] == level1 and row['level_2'] == level2)

        # 生成插入语句
        insert_kind.append(
            f"INSERT INTO cms_kind_1 (id, kind, parent_id, level, sort) VALUES ({current_id}, '{level2}', {parent_id}, 2, {min_sort_2});")
        kind_id_map[(level1, level2)] = current_id
        current_id += 1

# 第三层：level_3
for level1, level2, level3 in unique_level3:
    parent_id = kind_id_map.get((level1, level2), None)
    if parent_id is not None:
        # 找到 level3 对应的最小 sort_3
        min_sort_3 = min(row['sort_3'] for row in data if
                         row['level_1'] == level1 and row['level_2'] == level2 and row['level_3'] == level3)

        # 生成插入语句
        insert_kind.append(
            f"INSERT INTO cms_kind_1 (id, kind, parent_id, level, sort) VALUES ({current_id}, '{level3}', {parent_id}, 3, {min_sort_3});")
        kind_id_map[(level1, level2, level3)] = current_id
        current_id += 1

# 打印所有插入语句
for stmt in insert_kind:
    print(stmt)